﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Data.Linq;
using System.Data.Common;
using System.Globalization;
using System.Reflection;

namespace GoodStuff.Data.Linq
{
    /// <summary>
    /// Extensions for Linq To SQL
    /// </summary>
    public static class HeavyOperationExtensions
    {
        /// <summary>
        /// Copy the dataset by using SQL Bulk methods. (default timeout = 90 seconds)
        /// </summary>
        /// <typeparam name="TEntity">The type of objects</typeparam>
        /// <param name="table"></param>
        /// <param name="entities"></param>
        /// <param name="transaction">DB Transaction</param>
        /// <remarks>Make sure the order of the columns in your table and within LINQ-mapping are equal!</remarks>
        public static void BulkCopy<TEntity>(this Table<TEntity> table, IEnumerable<TEntity> entities, DbTransaction transaction) where TEntity:class
        {
            BulkCopy<TEntity>(table, entities, transaction, 90);
        }

        /// <summary>
        /// Copy the dataset by using SQL Bulk methods. (default timeout = 90 seconds)
        /// </summary>
        /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
        /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
        /// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param>
        /// <param name="transaction">DB Transaction</param>
        /// <param name="timeout">Timeout in seconds</param>
        /// <remarks>
        /// <para>Similiar to stored procedures, and opposite from InsertAllOnSubmit, rows provided in <paramref name="entities"/> will be inserted immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para>
        /// <para>Additionally, to improve performance, instead of creating a insert command for each item in <paramref name="entities"/>, a single bulk insert command is created.</para>
        /// <para>Make sure the order of the columns in your table and within LINQ-mapping are the same! Otherwise the mapping fails.</remarks></para>
        /// </remarks>
        public static void BulkCopy<TEntity>(this Table<TEntity> table, IEnumerable<TEntity> entities, DbTransaction transaction, int timeout) where TEntity : class
        {
            SqlBulkCopy bulk;
            if (transaction == null)
            {
                bulk = new SqlBulkCopy(table.Context.Connection.ConnectionString);
            }
            else
            {
                if (table.Context.Connection.State != ConnectionState.Open)
                {
                    table.Context.Connection.Open();
                }
                bulk = new SqlBulkCopy((SqlConnection)table.Context.Connection, SqlBulkCopyOptions.Default, (SqlTransaction)transaction);
                bulk.BulkCopyTimeout = timeout; //Heavy operation, so give it some time
            }

            HeavyOperationBulkCopyReader<TEntity> reader = new HeavyOperationBulkCopyReader<TEntity>(entities);

            foreach (var column in reader.ColumnMappingList)
            {
                bulk.ColumnMappings.Add(column.Key, column.Value);
            }
            bulk.DestinationTableName = reader.TableName;

            bulk.WriteToServer(reader);
        }

        /// <summary>
        /// Immediately deletes all entities from the collection with a single delete command. (default timeout = 90 seconds)
        /// </summary>
        /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
        /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
        /// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param>
        /// <returns>The number of rows deleted from the database.</returns>
        /// <remarks>
        /// <para>Similiar to stored procedures, and opposite from DeleteAllOnSubmit, rows provided in <paramref name="entities"/> will be deleted immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para>
        /// <para>Additionally, to improve performance, instead of creating a delete command for each item in <paramref name="entities"/>, a single delete command is created.</para>
        /// </remarks>
        public static int DeleteBatch<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class
        {
            return DeleteBatch<TEntity>(table, entities, 90);
        }

        /// <summary>
        /// Immediately deletes all entities from the collection with a single delete command.
        /// </summary>
        /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
        /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
        /// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param>
        /// <param name="timeout">Timeout in seconds</param>
        /// <returns>The number of rows deleted from the database.</returns>
        /// <remarks>
        /// <para>Similiar to stored procedures, and opposite from DeleteAllOnSubmit, rows provided in <paramref name="entities"/> will be deleted immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para>
        /// <para>Additionally, to improve performance, instead of creating a delete command for each item in <paramref name="entities"/>, a single delete command is created.</para>
        /// </remarks>
        public static int DeleteBatch<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities, int timeout) where TEntity : class
        {
            DbCommand delete = table.GetDeleteBatchCommand<TEntity>(entities);
            delete.CommandTimeout = timeout;

            var parameters = from p in delete.Parameters.Cast<DbParameter>()
                             select p.Value;

            return table.Context.ExecuteCommand(delete.CommandText, parameters.ToArray());
        }

        /// <summary>
        /// Deletes all items from a table. (default timeout = 90 seconds)
        /// </summary>
        /// <typeparam name="TEntity">Type to delete all items from</typeparam>
        /// <param name="table">the table to empty</param>
        /// <returns>number of rows deleted</returns>
        public static int DeleteBatch<TEntity>(this Table<TEntity> table) where TEntity : class
        {
            return DeleteBatch<TEntity>(table, 90);
        }

        /// <summary>
        /// Deletes all items from a table
        /// </summary>
        /// <typeparam name="TEntity">Type to delete all items from</typeparam>
        /// <param name="table">the table to empty</param>
        /// <param name="timeout">timeout in seconds</param>
        /// <returns>number of rows deleted</returns>
        public static int DeleteBatch<TEntity>(this Table<TEntity> table, int timeout) where TEntity : class
        {
            DbCommand deleteCommand = table.Context.GetCommand(table);
            deleteCommand.CommandText = string.Format("DELETE FROM {0}\r\n", table.GetDbName());
            deleteCommand.CommandTimeout = timeout;

            var parameters = from p in deleteCommand.Parameters.Cast<DbParameter>()
                             select p.Value;

            return table.Context.ExecuteCommand(deleteCommand.CommandText, parameters.ToArray());
        }

        /// <summary>
        /// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.
        /// </summary>
        /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam>
        /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param>
        /// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param>
        /// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns>
        /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
        public static string DeleteBatchPreview<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class
        {
            DbCommand delete = table.GetDeleteBatchCommand<TEntity>(entities);
            return delete.PreviewCommandText(false) + table.Context.GetLog();
        }

        #region HelperExtenions : http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx

        private static DbCommand GetDeleteBatchCommand<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class
        {
            var deleteCommand = table.Context.GetCommand(entities);
            deleteCommand.CommandText = string.Format("DELETE {0}\r\n", table.GetDbName()) + GetBatchJoinQuery<TEntity>(table, entities);
            return deleteCommand;
        }

        /// <summary>
        /// Returns a string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.
        /// </summary>
        /// <param name="cmd">The <see cref="DbCommand"/> to analyze.</param>
        /// <param name="forTransactSQL">Whether or not the text should be formatted as 'logging' similiar to LINQ to SQL output, or in valid Transact SQL syntax ready for use with a 'query analyzer' type tool.</param>
        /// <returns>Returns a string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.</returns>
        /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks>
        private static string PreviewCommandText(this DbCommand cmd, bool forTransactSQL)
        {
            var output = new StringBuilder();

            if (!forTransactSQL) output.AppendLine(cmd.CommandText);

            foreach (DbParameter parameter in cmd.Parameters)
            {
                int num = 0;
                int num2 = 0;
                PropertyInfo property = parameter.GetType().GetProperty("Precision");
                if (property != null)
                {
                    num = (int)Convert.ChangeType(property.GetValue(parameter, null), typeof(int), CultureInfo.InvariantCulture);
                }
                PropertyInfo info2 = parameter.GetType().GetProperty("Scale");
                if (info2 != null)
                {
                    num2 = (int)Convert.ChangeType(info2.GetValue(parameter, null), typeof(int), CultureInfo.InvariantCulture);
                }
                SqlParameter parameter2 = parameter as SqlParameter;

                if (forTransactSQL)
                {
                    output.AppendFormat("DECLARE {0} {1}{2}; SET {0} = {3}\r\n",
                        new object[] { 
								parameter.ParameterName, 
								( parameter2 == null ) ? parameter.DbType.ToString() : parameter2.SqlDbType.ToString(), 
								( parameter.Size > 0 ) ? "( " + parameter.Size.ToString( CultureInfo.CurrentCulture ) + " )" : "", 
								GetParameterTransactValue( parameter, parameter2 ) });
                }
                else
                {
                    output.AppendFormat("-- {0}: {1} {2} (Size = {3}; Prec = {4}; Scale = {5}) [{6}]\r\n", new object[] { parameter.ParameterName, parameter.Direction, (parameter2 == null) ? parameter.DbType.ToString() : parameter2.SqlDbType.ToString(), parameter.Size.ToString(CultureInfo.CurrentCulture), num, num2, (parameter2 == null) ? parameter.Value : parameter2.SqlValue });
                }
            }

            if (forTransactSQL) output.Append("\r\n" + cmd.CommandText);

            return output.ToString();
        }

        private static string GetParameterTransactValue(DbParameter parameter, SqlParameter parameter2)
        {
            if (parameter2 == null) return parameter.Value.ToString(); // Not going to deal with NON SQL parameters.

            switch (parameter2.SqlDbType)
            {
                case SqlDbType.Char:
                case SqlDbType.Date:
                case SqlDbType.DateTime:
                case SqlDbType.DateTime2:
                case SqlDbType.NChar:
                case SqlDbType.NText:
                case SqlDbType.NVarChar:
                case SqlDbType.SmallDateTime:
                case SqlDbType.Text:
                case SqlDbType.VarChar:
                case SqlDbType.UniqueIdentifier:
                    return string.Format("'{0}'", parameter2.SqlValue);

                default:
                    return parameter2.SqlValue.ToString();
            }
        }

        private static string GetLog(this DataContext context)
        {
            PropertyInfo providerProperty = context.GetType().GetProperty("Provider", BindingFlags.Instance | BindingFlags.NonPublic);
            var provider = providerProperty.GetValue(context, null);
            Type providerType = provider.GetType();

            PropertyInfo modeProperty = providerType.GetProperty("Mode", BindingFlags.Instance | BindingFlags.NonPublic);
            FieldInfo servicesField = providerType.GetField("services", BindingFlags.Instance | BindingFlags.NonPublic);
            object services = servicesField != null ? servicesField.GetValue(provider) : null;
            PropertyInfo modelProperty = services != null ? services.GetType().GetProperty("Model", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.GetProperty) : null;

            return string.Format("-- Context: {0}({1}) Model: {2} Build: {3}\r\n",
                            providerType.Name,
                            modeProperty != null ? modeProperty.GetValue(provider, null) : "unknown",
                            modelProperty != null ? modelProperty.GetValue(services, null).GetType().Name : "unknown",
                            "3.5.21022.8");
        }

        private static string GetDbName<TEntity>(this Table<TEntity> table) where TEntity : class
        {
            var entityType = typeof(TEntity);
            var metaTable = table.Context.Mapping.GetTable(entityType);
            var tableName = metaTable.TableName;

            if (!tableName.StartsWith("["))
            {
                string[] parts = tableName.Split('.');
                tableName = string.Format("[{0}]", string.Join("].[", parts));
            }

            return tableName;
        }

        private static string GetBatchJoinQuery<TEntity>(Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class
        {
            var metaTable = table.Context.Mapping.GetTable(typeof(TEntity));

            var keys = from mdm in metaTable.RowType.DataMembers
                       where mdm.IsPrimaryKey
                       select new { mdm.MappedName };

            var joinSB = new StringBuilder();
            var subSelectSB = new StringBuilder();

            foreach (var key in keys)
            {
                joinSB.AppendFormat("j0.[{0}] = j1.[{0}] AND ", key.MappedName);
                // For now, always assuming table is aliased as t0.  Should probably improve at some point.
                // Just writing a smaller sub-select so it doesn't get all the columns of data, but instead
                // only the primary key fields used for joining.
                subSelectSB.AppendFormat("[t0].[{0}], ", key.MappedName);
            }

            var selectCommand = table.Context.GetCommand(entities);
            var select = selectCommand.CommandText;

            var join = joinSB.ToString();

            if (join == "")
            {
                throw new MissingPrimaryKeyException(string.Format("{0} does not have a primary key defined.  Batch updating/deleting can not be used for tables without a primary key.", metaTable.TableName));
            }

            join = join.Substring(0, join.Length - 5);												// Remove last ' AND '
            var selectClause = select.Substring(0, select.IndexOf("[t0]"));							// Get 'SELECT ' and any TOP clause if present
            var needsTopClause = selectClause.IndexOf(" TOP ") < 0 && select.IndexOf("\r\nORDER BY ") > 0;
            var subSelect = selectClause
                                + (needsTopClause ? "TOP 100 PERCENT " : "")							// If order by in original select without TOP clause, need TOP
                                + subSelectSB.ToString();												// Apped just the primary keys.
            subSelect = subSelect.Substring(0, subSelect.Length - 2);									// Remove last ', '

            subSelect += select.Substring(select.IndexOf("\r\nFROM ")); // Create a sub SELECT that *only* includes the primary key fields

            var batchJoin = String.Format("FROM {0} AS j0 INNER JOIN (\r\n\r\n{1}\r\n\r\n) AS j1 ON ({2})\r\n", table.GetDbName(), subSelect, join);
            return batchJoin;
        }

        #endregion
    }
}
